package JDBC_1.Blob;

import JDBC_1.bean.Customer;
import JDBC_1.util.JDBCUtils;
import org.junit.jupiter.api.Test;

import java.io.*;
import java.sql.*;

/**
 * @author ：Yan Guang
 * @date ：Created in 2020/5/14 15:09
 * @description： 用preparedsatement来操作blob类型数据
 */
public class Blobtest {
    //插入操作
    @Test
    public void testInsert() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql ="insert into customers(name,email,birth,photo)values(?,?,?,?)";
        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setObject(1,"广哥2");
        ps.setObject(2,"yanguang@qq.com");
        ps.setObject(3,"2000-02-05");
        FileInputStream is=new FileInputStream(new File("Job.jpg"));
        ps.setBlob(4,is);

        ps.execute();

        JDBCUtils.closeResourece(connection,ps);

    }

    //查询customers中的blob字段
    @Test
    public void testQuery() {
        Connection connection = null;
        PreparedStatement ps = null;
        InputStream is = null;
        FileOutputStream fos = null;
        ResultSet rs = null;
        try {
            connection = JDBCUtils.getConnection();
            String sql="select id,name,email,birth,photo from customers where id = ?";
            ps = connection.prepareStatement(sql);
            ps.setObject(1,32);
            rs = ps.executeQuery();
            if (rs.next()){
                int id  = (int) rs.getObject(1);
                String name  = (String) rs.getObject(2);
                String email = rs.getString(3);
                Date birth = rs.getDate(4);
                Customer cust =new Customer(id,name,email,birth);
                System.out.println(cust);
                //讲blob下载下载保存在本地
                Blob photo = rs.getBlob(5);
                is= photo.getBinaryStream();
                fos=new FileOutputStream("wode.jpg");
                byte[] buffer=new byte[1024];
                int len;
                while ((len = is.read(buffer))!=-1){
                    fos.write(buffer,0,len);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                if (is!=null)
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {if (fos!=null)
                fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            JDBCUtils.closeResourece(connection,ps,rs);
        }
    }
}
